﻿/******************************************************   
创建者: 曹亚亮
创建时间: 2017-04-13
修改:
修改时间: 
功能: 企划预算金额
 ******************************************************/

using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using Nancy.Extensions;
using NHibernateVertica;
using PlanAPI.model;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VerticaAPI;

namespace PlanAPI
{
    [Description("企划预算金额")]
    public class PLBasBudgetAmountHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        /// Writer：曹亚亮
        /// Create Date：2017-04-13
        [MustLogin]
        [Description("企划预算金额列表")]
        [ReturnType(typeof(PaperData<PLBasBudgetAmountModel.ListModel>))]
        public ApiResult GetPLBasSalePlanList([ParamDescription("商品区域")] string ParentAreaID = "", [ParamDescription("类别")] string KindID = "", [ParamDescription("店铺代码或名称")] string Keys = "",
            [ParamDescription("页码")] int Page = 1, [ParamDescription("页数")] int Size = 10)
        {
            try
            {
                var count = ReturnPLBasBudgetAmountCount(ParentAreaID, KindID, Keys);
                var dt = ReturnPLBasBudgetAmountList(ParentAreaID, KindID, Keys, Page, Size);
                var result = PaperData<PLBasBudgetAmountModel.ListModel>.Create(count, Size, Page);
                var list = ModelConvertHelper<PLBasBudgetAmountModel.ListModel>.ConvertToModel(dt);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        /// Writer：曹亚亮
        /// Create Date：2017-04-13
        /// <summary>
        /// 统计企划预算金额行数
        /// </summary>
        /// <param name="ParentAreaID">商品区域</param>
        /// <param name="KindID">类别ID</param>
        /// <param name="Keys">店铺代码或名称</param>
        /// <returns>返回企划预算金额总行数</returns>
        public int ReturnPLBasBudgetAmountCount(string ParentAreaID, string KindID, string Keys)
        {
            try
            {
                if (!string.IsNullOrEmpty(Keys.Trim()))
                {
                    Keys = "'" + Keys.Replace("，", ",").TrimEnd(',').TrimStart(',').Replace(",", "','") + "'";
                }
                var sql = $@"SELECT COUNT(1) FROM(
SELECT 
	BBA.ShopID,
	BBA.Yearv,
	BBA.Monthv,
	DDS.ShopCode,
	DDS.ShopName,
	ROW_NUMBER()OVER(ORDER BY BBA.Yearv desc,BBA.Monthv desc,DDS.ShopCode) AS Row
FROM 
	ngoods.ngoods_PL_Bas_BudgetAmount AS BBA, 
	ngoods.ngoods_PL_ShopKindRate AS SKR, 
	nods.nods_erp_SD_Mat_Kind AS SMK,
	ngoods.ngoods_PL_Bas_SeasonMonth AS BSM,
	ndw.ndw_Shop AS DDS,
	(SELECT 
		Yearv,
		Monthv 
	FROM ngoods.ngoods_PL_PlanYMSet 
	WHERE Executint=1 and ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}') AS YMS
WHERE 1=1 and bba.ExtrSource='{UserInfo.UserExtrSource}' and bba.CompanyID='{UserInfo.UserCompanyId}'
	AND BBA.Yearv=YMS.Yearv
	AND BBA.Monthv=YMS.Monthv
	AND BBA.ShopID=SKR.ShopID  	AND BBA.ExtrSource=SKR.ExtrSource	AND BBA.CompanyID=SKR.CompanyID
	AND BBA.Yearv=SKR.Yearv
	AND SKR.SeasonID=BSM.SeasonID  	AND BSM.ExtrSource=SKR.ExtrSource	AND BSM.CompanyID=SKR.CompanyID
	AND SKR.ParentAreaID=BSM.ParentAreaID
	AND BBA.ShopID=DDS.ShopID  	AND BBA.ExtrSource=DDS.ExtrSource	AND BBA.CompanyID=DDS.CompanyID
	AND SKR.KindID=SMK.KindID AND SKR.ExtrSource=SMK.ExtrSource	
	AND BBA.Monthv=BSM.Monthv AND BSM.ExtrSource=BBA.ExtrSource	AND BSM.CompanyID=BBA.CompanyID
    {(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $@" AND SKR.ParentAreaID='{ParentAreaID}' ")}
    {(string.IsNullOrWhiteSpace(KindID) ? "" : $@" AND SKR.KindID='{KindID}' ")}
    {(string.IsNullOrWhiteSpace(Keys) ? "" : $@" AND DDS.ShopID IN ({Keys}) ")}
GROUP BY BBA.ShopID,BBA.Yearv,BBA.Monthv,DDS.ShopCode,DDS.ShopName
) CountRow";

                //{ (string.IsNullOrWhiteSpace(Keys) ? "" : $@" AND (DDS.ShopCode='{Keys}' OR DDS.ShopName LIKE '%{Keys}%') ")}
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// Writer：曹亚亮
        /// Create Date：2017-04-13
        /// <summary>
        /// 企划预算金额表
        /// </summary>
        /// <param name="ParentAreaID">商品区域</param>
        /// <param name="KindID">类别ID</param>
        /// <param name="Keys">店铺代码或名称</param>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <returns>返回企划预算金额列表</returns>
        public DataTable ReturnPLBasBudgetAmountList(string ParentAreaID, string KindID, string Keys, int Page, int Size)
        {
            try
            {
                if (!string.IsNullOrEmpty(Keys.Trim()))
                {
                    Keys = "'" + Keys.Replace("，", ",").TrimEnd(',').TrimStart(',').Replace(",", "','") + "'";
                }
                var sql = $@"SELECT 
	BBA.Yearv ||'-'|| BBA.Monthv AS Yearv,
    SKR.ParentAreaID,
	DDS.ShopCode,
	DDS.ShopName,
	SMK.KindName,
	SKR.CkPtStandAmountRate * 100 AS CkPtStandAmountRate,
	BBA.BudgetAmount,
	SKR.CkPtStandAmountRate*BBA.BudgetAmount AS MulAmount,
	ROW_NUMBER()OVER(ORDER BY BBA.Yearv desc,BBA.Monthv desc,DDS.ShopCode,SMK.KindName) AS Row
FROM 
	ngoods.ngoods_PL_Bas_BudgetAmount AS BBA, 
	ngoods.ngoods_PL_ShopKindRate AS SKR, 
	nods.nods_erp_SD_Mat_Kind AS SMK,
	ngoods.ngoods_PL_Bas_SeasonMonth AS BSM,
	ndw.ndw_Shop AS DDS,
	(
	SELECT * FROM(
	SELECT 
		BBA.ShopID,
		BBA.Yearv,
		BBA.Monthv,
		DDS.ShopCode,
		DDS.ShopName,
		ROW_NUMBER()OVER(ORDER BY BBA.Yearv desc,BBA.Monthv desc,DDS.ShopCode) AS Row
	FROM 
		ngoods.ngoods_PL_Bas_BudgetAmount AS BBA, 
	ngoods.ngoods_PL_ShopKindRate AS SKR, 
	nods.nods_erp_SD_Mat_Kind AS SMK,
	ngoods.ngoods_PL_Bas_SeasonMonth AS BSM,
	ndw.ndw_Shop AS DDS,
	    (SELECT 
		    Yearv,
		    Monthv 
	    FROM ngoods.ngoods_PL_PlanYMSet 
	    WHERE Executint=1 and ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}') AS YMS
	WHERE 1=1  and bba.ExtrSource='{UserInfo.UserExtrSource}' and bba.CompanyID='{UserInfo.UserCompanyId}'
	    AND BBA.Yearv=YMS.Yearv
	AND BBA.Monthv=YMS.Monthv
	AND BBA.ShopID=SKR.ShopID  	AND BBA.ExtrSource=SKR.ExtrSource	AND BBA.CompanyID=SKR.CompanyID
	AND BBA.Yearv=SKR.Yearv
	AND SKR.SeasonID=BSM.SeasonID  	AND BSM.ExtrSource=SKR.ExtrSource	AND BSM.CompanyID=SKR.CompanyID
	AND SKR.ParentAreaID=BSM.ParentAreaID
	AND BBA.ShopID=DDS.ShopID  	AND BBA.ExtrSource=DDS.ExtrSource	AND BBA.CompanyID=DDS.CompanyID
	AND SKR.KindID=SMK.KindID AND SKR.ExtrSource=SMK.ExtrSource	
	AND BBA.Monthv=BSM.Monthv AND BSM.ExtrSource=BBA.ExtrSource	AND BSM.CompanyID=BBA.CompanyID
        {(string.IsNullOrWhiteSpace(ParentAreaID) ? "" : $@" AND SKR.ParentAreaID='{ParentAreaID}' ")}
		{(string.IsNullOrWhiteSpace(KindID) ? "" : $@" AND SKR.KindID='{KindID}' ")}
		{(string.IsNullOrWhiteSpace(Keys) ? "" : $@" AND DDS.ShopID IN({Keys}) ")}
	GROUP BY BBA.ShopID,BBA.Yearv,BBA.Monthv,DDS.ShopCode,DDS.ShopName
	) table1 where Row BETWEEN ({Page}-1)*{Size}+1 AND {Page}*{Size}
	) GBC
WHERE 1=1  and bba.ExtrSource='{UserInfo.UserExtrSource}' and bba.CompanyID='{UserInfo.UserCompanyId}'
    AND BBA.ShopID=GBC.ShopID
	AND BBA.Yearv=GBC.Yearv
	AND BBA.Monthv=GBC.Monthv
	AND BBA.ShopID=SKR.ShopID 	AND BBA.ExtrSource=SKR.ExtrSource	AND BBA.CompanyID=SKR.CompanyID
	AND BBA.Yearv=SKR.Yearv
	AND SKR.SeasonID=BSM.SeasonID  	AND BSM.ExtrSource=SKR.ExtrSource	AND BSM.CompanyID=SKR.CompanyID
	AND SKR.ParentAreaID=BSM.ParentAreaID
	AND BBA.ShopID=DDS.ShopID  	AND BBA.ExtrSource=DDS.ExtrSource	AND BBA.CompanyID=DDS.CompanyID
	AND SKR.KindID=SMK.KindID AND SKR.ExtrSource=SMK.ExtrSource	
	AND BBA.Monthv=BSM.Monthv AND BSM.ExtrSource=BBA.ExtrSource	AND BSM.CompanyID=BBA.CompanyID
		{(string.IsNullOrWhiteSpace(KindID) ? "" : $@" AND SKR.KindID='{KindID}' ")}";

                //{ (string.IsNullOrWhiteSpace(Keys) ? "" : $@" AND (DDS.ShopCode='{Keys}' OR DDS.ShopName LIKE '%{Keys}%') ")}
                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

    }
}
